<?php
namespace App\Http\Controllers\CRM;
use App\Order;
use App\Rad_user;
use App\Rad_user_account;
use Illuminate\Http\Request;

use App\Common\ErrorDefine;

use App\Radacct;
use App\Radcheck;
use App\Radgroupcheck;
use App\Radreply;
use App\Radusergroup;
use App\Rad_manage;
use App\Radgroupreply;
use App\Rad_users;
use App\Rad_account;
use App\Rad_merge_data;
use DB;
use App\Rad_manage_discount;
use App\Rad_order;
use App\Line;
use App\Agent;
use App\Teamqq;

use App\Manage_user_list;
use App\Discount;
use App\Http\Controllers\Controller;
use Redis;
use App\Terrace;
use App\Http\Controllers\CRM\PublicController;

class GetDataController extends Controller
{



    /**
     * @param Request $request
     * 获取官网用户列表
     * param username用户民（可选）
     * param start_date 查询用户注册开始时间（可选）
     * param end_date  查询用户民注册结束时间（可选）
     * param expired_type 到期类型  查询用户到期类型（可选）
     */
    public function getWebAccountListData(Request $request)
    {

        $start_date = "1970-07-01";
        $end_date = date('Y-m-d H:i:s',time());
        $username = '';
        $sort_name = 'created_at';
        $sort_value = 'desc';
        $page_no = 0;
        $page_size = 20;


        //   用户token
        if(!$request->has('token') || empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');


        $public = new PublicController();

        //   获取用户ID
        $user = $public->getTokenById($token);

        if(empty($user))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }



        if($request->has('end_date') && !empty($request->input('end_date')))
        {
            $end_date = $request->input('end_date');
        }

        if($request->has('start_date') && !empty($request->input('start_date')))
        {
            $start_date = $request->input('start_date');
        }

        $expired_at="2099-10-01";
        if($request->has('expired_at') && !empty($request->input('expired_at')))
        {
            switch($request->input('expired_at'))
            {
                case '近3天': $expired_at = date('Y-m-d H:i:s',time()+86400*3); $end_date= date('Y-m-d H:i:s',time()+86400*3);break;
                case '近7天': $expired_at = date('Y-m-d H:i:s',time()+86400*7);$end_date = date('Y-m-d H:i:s',time()+86400*7);break;
                case '已到期': $expired_at = date('Y-m-d H:i:s',time());$end_date = date('Y-m-d H:i:s',time());break;
            }
        }


        if($request->has('sort_name') && !empty($request->input('sort_name')))
        {
            $sort_name = $request->input('sort_name');

        }

        if($request->has('sort_value') && !empty($request->input('sort_value')))
        {
            $sort_value = $request->input('sort_value');
        }




        if($request->has('username') && !empty($request->input('username')))
        {
            $username = $request->input('username');
        }

        if (preg_match("/[\x7f-\xff]/", $username))
        {
            self::ShowError(ErrorDefine::INVALID_USERNAME);
            return;
        }


        if($request->has('page_no'))
        {
            $page_no  = $request->input('page_no');
            if($page_no){
                $page_no = $page_no - 1;
                if($page_no<0){
                    $page_no = 0;
                }
            }
        }

        if($request->has('page_size'))
        {
            $page_size = $request->input('page_size');
            if($page_size){
                if($page_size <=0 ){
                    $page_size =  20;
                }
            }
        }
        $manage_id = $user[0]['manage_id'];
        $level= $user[0]['level'];


        if($level == 2)
        {
            switch($sort_name)
            {
                case 'expired_num':$arr = self::getSuperWebAccountExpiredNumSort($username,$page_no,$page_size,$start_date,$end_date,$sort_name,$sort_value,$expired_at);break;
                case 'balance':$arr = self::getSuperWebAccountBalanceSort($username,$page_no,$page_size,$start_date,$end_date,$sort_value,$expired_at);break;
                default:$arr = self::getSuperWebValidAccountData($username,$sort_name,$sort_value,$page_no,$page_size,$start_date,$end_date,$expired_at);break;
            }
        }else{

            switch($sort_name)
            {
                case 'expired_num':$arr = self::getServiceWebAccountExpiredNumSort($username,$page_no,$page_size,$start_date,$end_date,$sort_name,$sort_value,$expired_at,$manage_id);break;
                case 'balance':$arr = self::getServerAccountBalanceSort($username,$page_no,$page_size,$start_date,$end_date,$sort_value,$expired_at,$manage_id);break;
                default:$arr = self::getServiceWebValidAccountData($username,$sort_name,$sort_value,$page_no,$page_size,$start_date,$end_date,$expired_at,$manage_id);break;
            }
        }



        $count = $arr['count'];
        $users = $arr['users'];

        $page_number =  ceil($count/$page_size);

        $page_no = $page_no + 1;
        $data = [
            'total'=>$count,
            'page_number'=>$page_number,
            'page_no'=>$page_no,
            'users'=>$users,
        ];


        return self::ShowMessage($data);

    }


    static function getSuperWebAccountBalanceSort($username,$page_no,$page_size,$start_date,$end_date,$sort_value,$expired_at)
        {

            $page_no = $page_no*$page_size;
            $time = date('Y-m-d H:i:s',time());
            $parent = [];
            $sql="select pid, user_id,expired_at,balance,days from users
                   where users.pid in(select pid from users where pid !=0 and CONCAT(username,mobile) like '%{$username}%'
                    and expired_at > '{$start_date}' GROUP BY pid )
                   and days >0 and balance >0 GROUP BY users.user_id  limit $page_no,$page_size";

            $user = DB::connection('mysql_concent')->select("$sql");

            foreach($user as $item)
            {
                if(array_key_exists($item->pid,$parent))
                {
                    $parent["$item->pid"] = $parent["$item->pid"]+ceil((strtotime($item->expired_at)-time())/86400) * ($item->balance/$item->days);
                }else{
                    $parent["$item->pid"] = ceil((strtotime($item->expired_at)-time())/86400) * ($item->balance/$item->days);

                }
            }
            if($sort_value == 'desc')
            {
                arsort($parent);
            }else{
                asort($parent);
            }

            $count = count($parent);

            array_slice($parent,$page_no,$page_size,true);

            $i = 0;
            $arr = [];
            foreach($parent as $key=>$item)
            {

                $users = Rad_users::where('user_id',$key)->get(['user_id','username','mobile','manage_id','remark','qq','account_num','total_consumption','communication_time','created_at'])->toArray();
                if($users[0]['manage_id'])
                {
                    $manage = Rad_manage::find($users[0]['manage_id']);
                    $manage_name = $manage->username;
                }else{
                    $manage_name =  '';
                }


                $users[0]['manage_name'] = $manage_name;
                $users[0]['expired_num'] = Rad_users::where('pid',$key)->where('expired_at','<',$end_date)->count();
                $rad = Rad_users::where('pid',$key)->where('expired_at','<',$time)->orderBy('expired_at','desc')->first();
                if($rad != NULL)
                {
                    $users[0]['expired_at'] =$rad->expired_at;
                }else{
                    $users[0]['expired_at'] = '';
                }
                $users[0]['balance'] = $item;

                $arr['users'][$i]  =$users[0];
                $i++;

            }


            $arr['count']  =$count;
            return $arr;

        }

    static function getServerAccountBalanceSort($username,$page_no,$page_size,$start_date,$end_date,$sort_value,$expired_at,$manage_id)
    {
        $page_no = $page_no*$page_size;
        $time = date('Y-m-d H:i:s',time());
        $parent = [];
        $sql="select pid, user_id,expired_at,balance,days from users
                   where users.pid in(select pid from users where pid !=0 and CONCAT(username,mobile) like '%{$username}%'
                    and expired_at > '{$start_date}' GROUP BY pid  and manage_id = $manage_id)
                   and days >0 and balance >0 GROUP BY users.user_id  limit $page_no,$page_size";

        $user = DB::connection('mysql_concent')->select("$sql");

        foreach($user as $item)
        {
            if(array_key_exists($item->pid,$parent))
            {
                $parent["$item->pid"] = $parent["$item->pid"]+ceil((strtotime($item->expired_at)-time())/86400) * ($item->balance/$item->days);
            }else{
                $parent["$item->pid"] = ceil((strtotime($item->expired_at)-time())/86400) * ($item->balance/$item->days);

            }
        }
        if($sort_value == 'desc')
        {
            arsort($parent);
        }else{
            asort($parent);
        }

        $count = count($parent);

        array_slice($parent,$page_no,$page_size,true);

        $i = 0;
        $arr = [];
        foreach($parent as $key=>$item)
        {

            $users = Rad_users::where('user_id',$key)->get(['user_id','username','mobile','manage_id','remark','qq','account_num','total_consumption','communication_time','created_at'])->toArray();
            if($users[0]['manage_id'])
            {
                $manage = Rad_manage::find($users[0]['manage_id']);
                $manage_name = $manage->username;
            }else{
                $manage_name =  '';
            }


            $users[0]['manage_name'] = $manage_name;
            $users[0]['expired_num'] = Rad_users::where('pid',$key)->where('expired_at','<',$end_date)->count();
            $rad = Rad_users::where('pid',$key)->where('expired_at','<',$time)->orderBy('expired_at','desc')->first();
            if($rad != NULL)
            {
                $users[0]['expired_at'] =$rad->expired_at;
            }else{
                $users[0]['expired_at'] = '';
            }
            $users[0]['balance'] = $item;

            $arr['users'][$i]  =$users[0];
            $i++;

        }


        $arr['count']  =$count;
        return $arr;

    }



    /**
     * @param Request $request
     * param username用户民（可选）
     * param start_date 查询开号开始时间（可选）
     * param end_date  查询用开号注册结束时间（可选）
     * param 到期类型  查询用户到期类型（可选）
     */
    public function getWebChildAccountListData(Request $request)
    {



        $page_no = 0;
        $page_size =20;
        $username = '';
        $end_date = date('Y-m-d H:i:s',time());
        $start_date = '1970-01-01 08:00:00';
        $count = 0;
        $user_id = '';

        $result = [];


        $sql ="select user_id,username,password,qq,mobile,con_max ,created_at,balance,account_type,expired_at,set_meal_type,terrace_id,remark from users ";
        $sql_count = "select count(user_id) as num  from users";

        if($request->input('page_no'))
        {
            $page_no  = $request->input('page_no');
            if($page_no){
                $page_no = $page_no - 1;
                if(is_numeric($page_no)){
                    if($page_no<0){
                        $page_no = 0;
                    }
                }
            }
        }

        if($request->input('page_size'))
        {
            $page_size = $request->input('page_size');
            if($page_size){
                if($page_size <=0 ){
                    $page_size =  20;
                }
            }
        }


        if($request->has('user_id') && !empty($request->input('user_id')))
        {
            $user_id = $request->input('user_id');

            $user = Rad_users::find($user_id);

            if(!$user)
            {
                self::ShowError(ErrorDefine::USER_NOT_EXIST);
                return;
            }

            $sql .=" where pid = '{$user_id}'";
            $sql_count .=" where pid = '{$user_id}'";

        }else{
            self::ShowError(ErrorDefine::USER_NOT_EXIST);
            return;
        }
        // 查询时间
        if($request->has('end_date') && !empty($request->input('end_date')))
        {
            $end_date = $request->input('end_date');
        }

        $sql .=" and  created_at < '{$end_date}'";
        $sql_count .=" and  created_at < '{$end_date}'";

        if($request->has('start_date') && !empty($request->input('start_date')))
        {
            $start_date = $request->input('start_date');
        }

        $sql .=" and  created_at > '{$start_date}'";
        $sql_count .=" and  created_at > '{$start_date}'";


        if($request->has('username') && !empty($request->input('username')))
        {
            $username = trim($request->input('username'));
            $sql .=" and  username like '%{$username}%'";
            $sql_count .=" and  username like '%{$username}%'";
        }


        if($request->has('account_type') && !empty($request->input('account_type')) && $request->input('account_type')!="undefined")
        {
            $account_type = $request->input('account_type');
            $sql .=" and  account_type ='{$account_type}' ";
            $sql_count .=" and  account_type ='{$account_type}' ";
        }


        if($request->has('expired_at') && !empty($request->input('expired_at')))
        {

            $expired_at = $request->input('expired_at');

            $expired_start= date('Y-m-d H:i:s',time());

            switch($expired_at)
            {
                case "未到期":$sql .= " and expired_at > '{$expired_start}' ";
                    $sql_count .= " and expired_at > '{$expired_start}' "; break;

                case "已到期":$sql .= " and expired_at < '{$expired_start}' ";
                    $sql_count .= " and expired_at < '{$expired_start}' ";break;

                case "近三天到期":
                    $expired_end = date('Y-m-d H:i:s',time()+86400*3);
                    $sql .= " and expired_at < '{$expired_end}' and expired_at >'{$expired_start}' ";
                    $sql_count .= " and expired_at < '{$expired_end}' and expired_at >'{$expired_start}' ";break;

                case "近七天到期":
                    $expired_end = date('Y-m-d H:i:s',time()+86400*7);
                    $sql .= " and expired_at < '{$expired_end}' and expired_at >'{$expired_start}' ";
                    $sql_count .= " and expired_at < '{$expired_end}' and expired_at >'{$expired_start}' ";break;
                default:break;
            }

        }


        if($request->has('set_meal_type') && !empty($request->input('set_meal_type')))
        {
            $type = $request->input('set_meal_type');

            switch($type)
            {
                case 1: $sql .= " and set_meal_type = 1 ";$sql_count .= " and set_meal_type = 1 ";break;
                case 2: $sql .= " and set_meal_type = 2 ";$sql_count .= " and set_meal_type = 2 ";break;
                case 3: $sql .= " and set_meal_type = 3 ";$sql_count .= " and set_meal_type = 3 ";break;
                default:break;
            }

        }



        if($request->has('sort_name') && !empty($request->input('sort_name')))
        {
            $sort_name= $request->input('sort_name');
            $sort_value= $request->input('sort_value');

            $sql .= "   order by $sort_name $sort_value ";
        }else{
            $sql .= " order by created_at desc ";
        }


        $time = date('Y-m-d H:i:s',time());


        $user = new Rad_users();

        $no = $page_no*$page_size;
        $sql .= " limit $no,$page_size ";


//dd($sql);
        $arr = self::getChildData($sql,$sql_count);

        $users = $arr['users'];
        $count = $arr['count'];



        foreach($users as &$value)
        {

            $reply = Radreply::where('username',$value['username'])->first();
           if($reply != NULL){
               $value['speed'] = $reply->value;
           }else{
               $value['speed'] ='';
           }

            switch($value['set_meal_type'])
            {
                case 1:   $value['meal_type']='初级套餐'; break;
                case 2:   $value['meal_type']='中级套餐'; break;
                case 3:   $value['meal_type']='高级套餐'; break;
            }

            if(strtotime($value['expired_at']) - time() >= 0)
            {

                if(strtotime($value['expired_at']) - time() < 86400*2)
                {
                    $value['status'] ='即将到期';
                    $value['expired'] = 1;
                }else{
                    $value['status'] = '';
                    $value['expired'] = 0;
                }

            }else{
                $value['expired'] = 2;
                $value['status'] ='已到期';
            }
        }

        $page_number =  ceil($count/$page_size);


        $data = [
            'total'=>$count,
            'page_number'=>$page_number,
            'users'=>$users,
        ];


        return self::ShowMessage($data);


    }

    /**
     * @param $sql
     * @param $sql_count
     * @return array
     * 获取子级数据
     */

    static function getChildData($sql,$sql_count)
    {


        $users = DB::connection('mysql_concent')->select("$sql");

        $count = DB::connection('mysql_concent')->select("$sql_count");


        $res = [];
        foreach($users as $key=>$value)
        {
            $res[$key]['user_id'] = $value->user_id;
            $res[$key]['username'] = $value->username;
            $res[$key]['password'] = $value->password;
            $res[$key]['con_max'] = $value->con_max;
            $res[$key]['created_at'] = $value->created_at;
            $res[$key]['account_type'] = $value->account_type;
            $res[$key]['expired_at'] = $value->expired_at;
            $res[$key]['set_meal_type'] = $value->set_meal_type;
            $res[$key]['remark'] = $value->remark;
            $res[$key]['balance'] = $value->balance;
        }
        $arr = array(
            'users'=>$res,
            'count'=>$count[0]->num
        );


        return $arr;

    }

    /**
     * @param $username
     * @param $sort_name
     * @param $sort_value
     * @param $page_no
     * @param $page_size
     * @param $start_date
     * @param $end_date
     * @return array
     * 超管获取有效账户
     */
    static function getSuperWebValidAccountData($username,$sort_name,$sort_value,$page_no,$page_size,$start_date,$end_date,$expired_at)
    {

        $users = Rad_users::where(DB::Raw("CONCAT(username,mobile)"),'like',"%$username%" )
            ->where('mobile','!=','')->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('expired_at','<',$expired_at)
            ->orderBy($sort_name,$sort_value)->offset($page_no*$page_size)->limit($page_size)
            ->get(['user_id','username','mobile','manage_id','qq','remark','account_num','total_consumption','communication_time','created_at'])->toArray();

        $count = Rad_users::whereIn('terrace_id',[1,6])->where(DB::Raw("CONCAT(username,mobile)"),'like',"%$username%" )
            ->where('mobile','!=','')->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('expired_at','<',$expired_at)
            ->count();
        $id = '';
        foreach($users as $value)
        {
            $id .= $value['user_id'].',';
        }


        //  子账号到期时间
        $id = trim($id,',');
        $sql = "select  pid ,expired_at from users where pid in ($id)  GROUP by user_id order by expired_at desc ";

        $user_number = DB::connection('mysql_concent')->select("$sql");

        $res = [];
        foreach($user_number as $value)
        {
            $res["{$value->pid}"] =$value->expired_at;
        }

        //   子账号到期数量
        $sql = "select pid, count(user_id) as num from users where pid in ($id) and expired_at < '{$end_date}' GROUP by pid  ";


        $expired_number = DB::connection('mysql_concent')->select("$sql");

        $number = [];
        foreach($expired_number as $value)
        {
            $number["{$value->pid}"] =$value->num;
        }


        //  子账户可用余额
        $rad_user = new Rad_users();
        foreach($users as &$item)
        {
            $item['balance'] = 0;

            $ret = Rad_users::where('pid',$item['user_id'])->where('expired_at','>',$end_date)->where('days','>',0)
                ->where('balance','>',0)->where('account_type','付费')->get(['user_id','expired_at','days','balance']);

            foreach($ret as $rec)
            {
                $item['balance'] = $item['balance'] + floor( ceil((strtotime($rec->expired_at)-time())/86400) * (ceil($rec->balance)/$rec->days));
            }
        }


        $result = [];

        foreach($users as &$item)
        {
            if(array_key_exists($item['user_id'],$res))
            {
                $item['expired_at'] = $res["{$item['user_id']}"];
            }else{
                $item['expired_at'] = '';
            }


            if(array_key_exists($item['user_id'],$number))
            {
                $item['expired_num'] = $number["{$item['user_id']}"];
            }else{
                $item['expired_num'] = 0;
            }


           if($item['manage_id'])
           {
               $manage = Teamqq::where('manage_id',$item['manage_id'])->first();
              if($manage == NULL)
              {
                  $item['manage_name'] = '';
              }else{
                  $item['manage_name'] = $manage->username;
              }
           }else{
               $item['manage_name'] = '';
           }

            if($item['communication_time'] == 'null')
            {
                $item['communication_time'] =  '未沟通';
            }

        }
        $arr = [];

        $arr['users']  =$users;
        $arr['count']  =$count;
        return $arr;
    }

    /**
     * @param $username
     * @param $page_no
     * @param $page_size
     * @param $start_date
     * @param $end_date
     * @return array
     */
    static function getSuperWebAccountExpiredNumSort($username,$page_no,$page_size,$start_date,$end_date,$sort_name,$sort_value,$expired_at)
    {

        $page_no = $page_no*$page_size;

        $sql2 = "select  DISTINCT pid ,username,count(pid) as expired_num from users where
                  CONCAT(username,mobile) like '%{$username}%' and pid !=0 and created_at >'{$start_date}'
                 and created_at <'{$end_date}' and expired_at< '{$end_date}'   GROUP BY pid order by $sort_name $sort_value limit $page_no,$page_size";

        $sql = "select   count(DISTINCT pid) as count  from users where
                 CONCAT(username,mobile) like '%{$username}%' and pid !=0 and created_at >'{$start_date}'
                 and created_at <'{$end_date}' and expired_at< '{$end_date}' ";

        $user_number = DB::connection('mysql_concent')->select("$sql2");
        $user_count = DB::connection('mysql_concent')->select("$sql");

        $count =$user_count[0]->count;
        $res = [];
        foreach($user_number as $key=>&$value)
        {

            $users = Rad_users::where('user_id',$value->pid)->get(['user_id','username','mobile','manage_id','remark','qq','account_num','total_consumption','communication_time','created_at'])->toArray();
            if($users[0]['manage_id'])
            {
                $manage = Rad_manage::find($users[0]['manage_id']);
                $value->manage_name = $manage->username;
            }else{
                $value->manage_name =  '';
            }
            $value->balance = 0;


            $ret = Rad_users::where('pid',$value->pid)->where('expired_at','>',$end_date)->where('days','>',0)
                ->where('balance','>',0)->where('account_type','付费')->get(['user_id','expired_at','days','balance']);

            foreach($ret as $rec)
            {
                $value->balance = $value->balance + floor( ceil((strtotime($rec->expired_at)-time())/86400) * (ceil($rec->balance)/$rec->days));
            }


            if($users[0]['communication_time'] == 'null')
            {
                $users[0]['communication_time'] =  '未沟通';
            }

            $rad = Rad_users::where('pid',$value->pid)->where('expired_at','<',$end_date)->orderBy('expired_at','desc')->first();
            if($rad != NULL)
            {
                $users[0]['expired_at'] =$rad->expired_at;
            }else{
                $users[0]['expired_at'] = '';
            }


            $value->user_id = $users[0]['user_id'];
            $value->username = $users[0]['username'];
            $value->mobile = $users[0]['mobile'];
            $value->password = $users[0]['password'];
            $value->account_num = $users[0]['account_num'];
            $value->total_consumption = $users[0]['total_consumption'];
            $value->created_at = $users[0]['created_at'];
            $value->qq = $users[0]['qq'];
            $value->remark = $users[0]['remark'];
            $value->communication_time = $users[0]['communication_time'];
            $value->expired_at = $users[0]['expired_at'];
//            $value->balance = $balance;

        }

        $arr = [];

        $arr['users']  =$user_number;
        $arr['count']  =$count;

        return $arr;
    }


    /**
     * @param $username
     * @param $sort_name
     * @param $sort_value
     * @param $page_no
     * @param $page_size
     * @param $start_date
     * @param $end_date
     * @param $expired_at
     * @param $manage_id
     * 客服信息数据
     */
    static function getServiceWebValidAccountData($username,$sort_name,$sort_value,$page_no,$page_size,$start_date,$end_date,$expired_at,$manage_id)
    {
        $users = Rad_users::where(DB::Raw("CONCAT(username,mobile)"),'like',"%$username%" )->where('manage_id',$manage_id)
            ->where('mobile','!=','')->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('expired_at','<',$expired_at)
            ->orderBy($sort_name,$sort_value)->offset($page_no*$page_size)->limit($page_size)
            ->get(['user_id','username','mobile','manage_id','qq','remark','account_num','total_consumption','communication_time','created_at'])->toArray();

        $count = Rad_users::where(DB::Raw("CONCAT(username,mobile)"),'like',"%$username%" )->where('manage_id',$manage_id)
            ->where('mobile','!=','')->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('expired_at','<',$expired_at)
            ->count();
        $id = '';
        foreach($users as $value)
        {
            $id .= $value['user_id'].',';
        }


        //  子账号到期时间
        $id = trim($id,',');
        $sql = "select  pid ,expired_at from users where pid in ($id) and  expired_at < '{$end_date}' GROUP by user_id order by expired_at desc ";

        $user_number = DB::connection('mysql_concent')->select("$sql");

        $res = [];
        foreach($user_number as $value)
        {
            $res["{$value->pid}"] =$value->expired_at;
        }

        //   子账号到期数量
        $sql = "select pid, count(user_id) as num from users where pid in ($id) and expired_at < '{$end_date}' GROUP by pid  ";

        $expired_number = DB::connection('mysql_concent')->select("$sql");

        $number = [];
        foreach($expired_number as $value)
        {
            $number["{$value->pid}"] =$value->num;
        }

        //  子账户可用余额


        //  子账户可用余额
        foreach($users as &$item)
        {
            $item['balance'] = 0;

            $ret = Rad_users::where('pid',$item['user_id'])->where('expired_at','>',$end_date)->where('days','>',0)
                ->where('balance','>',0)->where('account_type','付费')->get(['user_id','expired_at','days','balance']);

            foreach($ret as $rec)
            {
                $item['balance'] = $item['balance'] + floor( ceil((strtotime($rec->expired_at)-time())/86400) * (ceil($rec->balance)/$rec->days));
            }
        }


        foreach($users as &$item)
        {
            if(array_key_exists($item['user_id'],$res))
            {
                $item['expired_at'] = $res["{$item['user_id']}"];
            }else{
                $item['expired_at'] = '';
            }


            if(array_key_exists($item['user_id'],$number))
            {
                $item['expired_num'] = $number["{$item['user_id']}"];
            }else{
                $item['expired_num'] = 0;
            }



            if($item['manage_id'])
            {
                $manage = Teamqq::where('manage_id',$item['manage_id'])->first();
                if($manage ==NULL)
                {
                    $item['manage_name'] = '';
                }else{
                    $item['manage_name'] = $manage->username;
                }
            }else{
                $item['manage_name'] = '';
            }

            if($item['communication_time'] == '0000-00-00 00:00:00')
            {
                $item['communication_time'] =  '未沟通';
            }

        }
        $arr = [];

        $arr['users']  =$users;
        $arr['count']  =$count;
        return $arr;
    }

    /**
     * @param $username
     * @param $page_no
     * @param $page_size
     * @param $start_date
     * @param $end_date
     * @param $sort_name
     * @param $sort_value
     * @param $expired_at
     * @param $manage_id
     */
    static  function getServiceWebAccountExpiredNumSort($username,$page_no,$page_size,$start_date,$end_date,$sort_name,$sort_value,$expired_at,$manage_id)
    {
        $page_no = $page_no*$page_size;
        $sql2="select  pid ,username,count(user_id) as expired_num from users where
                pid in( select user_id from users where manage_id = $manage_id  and pid !=0)
                 and CONCAT(username,mobile) like '%{$username}%' and created_at >'{$start_date}'
                 and created_at <'{$end_date}' and expired_at< '$end_date'
                  GROUP BY pid order by $sort_name $sort_value limit 0,20";

        $sql = "select   count(DISTINCT pid) as count  from users where  manage_id={$manage_id}
                 and CONCAT(username,mobile) like '%{$username}%' and pid !=0 and created_at >'{$start_date}'
                 and created_at <'{$end_date}' and expired_at< '{$expired_at}'  ";

        $user_number = DB::connection('mysql_concent')->select("$sql2");
        $user_count = DB::connection('mysql_concent')->select("$sql");

        $count =$user_count[0]->count;


        $res = [];
        foreach($user_number as $key=>&$value)
        {

            $users = Rad_users::where('user_id',$value->pid)->get(['user_id','username','mobile','remark','qq','expired_at','account_num','total_consumption','communication_time','created_at'])->toArray();

            $value->balance = 0;

            $ret = Rad_users::where('pid',$value->pid)->where('expired_at','>',$end_date)->where('days','>',0)
                ->where('balance','>',0)->where('account_type','付费')->get(['user_id','expired_at','days','balance']);

            foreach($ret as $rec)
            {
                $value->balance = $value->balance + floor( ceil((strtotime($rec->expired_at)-time())/86400) * (ceil($rec->balance)/$rec->days));
            }

            if($users[0]['communication_time'] == 'null')
            {
                $users[0]['communication_time'] =  '未沟通';
            }

            $rad = Rad_users::where('pid',$value->pid)->where('expired_at','<',$end_date)->orderBy('expired_at','desc')->first();
            if($rad != NULL)
            {
                $users[0]['expired_at'] =$rad->expired_at;
            }else{
                $users[0]['expired_at'] = '';
            }

            $value->user_id = $users[0]['user_id'];
            $value->username = $users[0]['username'];
            $value->mobile = $users[0]['mobile'];
            $value->password = $users[0]['password'];
            $value->account_num = $users[0]['account_num'];
            $value->total_consumption = $users[0]['total_consumption'];
            $value->created_at = $users[0]['created_at'];
            $value->qq = $users[0]['qq'];
            $value->remark = $users[0]['remark'];
            $value->expired_at =  $rad->expired_at;
            $value->communication_time =  $users[0]['communication_time'];

        }

        $arr = [];

        $arr['users']  =$user_number;
        $arr['count']  =$count;

        return $arr;
    }


    
    /**
     * @param Request $request
     * 获取未绑定的账号列表
     */
    public function getUnbindAccountList(Request $request)
    {

        $start_date = "1970-07-01";
        $end_date = date('Y-m-d H:i:s',time());
        $username = '';
        $page_no = 0;
        $page_size = 20;


        if($request->has('end_date') && !empty($request->input('end_date')))
        {
            $end_date = $request->input('end_date');
        }

        if($request->has('start_date') && !empty($request->input('start_date')))
        {
            $start_date = $request->input('start_date');
        }

        if($request->has('username') && !empty($request->input('username')))
        {
            $username = $request->input('username');
        }


        if (preg_match("/[\x7f-\xff]/", $username))
        {
            self::ShowError(ErrorDefine::INVALID_USERNAME);
            return;
        }


        if($request->has('page_no'))
        {
            $page_no  = $request->input('page_no');
            if($page_no){
                $page_no = $page_no - 1;
                if($page_no<0){
                    $page_no = 0;
                }
            }
        }

        if($request->has('page_size'))
        {
            $page_size = $request->input('page_size');
            if($page_size){
                if($page_size <=0 ){
                    $page_size =  20;
                }
            }
        }


        $count = Rad_users::where('pid',0)->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('username','like',"%{$username}%")->count();
        $users = Rad_users::where('pid',0)->where('created_at','>',$start_date)
                 ->where('created_at','<',$end_date)->where('username','like',"%{$username}%")
                 ->offset($page_no*$page_size)->limit($page_size)
                 ->get(['user_id','username','password','con_max','set_meal_type','expired_at','created_at']);

        foreach($users as &$user)
        {
            switch($user->set_meal_type)
            {
                case 1 : $user->meal_type = '基础套餐';break;
                case 2 : $user->meal_type = '中级套餐';break;
                case 3 : $user->meal_type = '高级套餐';break;
            }
        }


        $page_number =  ceil($count/$page_size);

        $data = [
            'total'=>$count,
            'page_number'=>$page_number,
            'page_no'=>$page_no+1,
            'users'=>$users,
        ];


        return self::ShowMessage($data);

    }

    /**
     * 超级管理员获取客服成交金额数据
     * param today  今日
     * param  month 本月
     * param  start_date
     * param end_date
     */
    public function getCustomerServiceTransactionAmountData(Request $request)
    {
        $start_date = date('Y-m-d',time());

        $end_date  = date('Y-m-d H:i:s',time());



        //   用户token
        if(!$request->has('token') || empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');


        $public = new PublicController();

        //   获取用户ID
        $user = $public->getTokenById($token);

        if(empty($user))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }
            //  日期类型
        if(!$request->has('date_type') || empty($request->input('date_type')))
        {
            self::ShowError(ErrorDefine::DATE_TYPE_NOT_EXIST);
            return;
        }

        $date_type =$request->input('date_type');
        switch($date_type)
        {
            case 'today':$start_date = date('Y-m-d',time());$end_date  = date('Y-m-d H:i:s',time());break;
            case 'month':$start_date = date('Y-m-01',time());$end_date  = date('Y-m-d H:i:s',time());break;
            default: self::ShowError(ErrorDefine::INVALID_DATE_TYPE);return;
        }

        $top_completion = 0;        //  最高金额
        $top_name = '';             //  最高金额名称

        $total_money = 0;
        $qq = Teamqq::where('manage_id','>','0')->get();
        foreach($qq as &$value)
        {

            $money = Rad_order::where('user_id',$value->manage_id)->where('created_at','>',$start_date)
                ->where('created_at','<',$end_date)->where('status',1)->sum('price');

            $value->today_money = $money;                               //   今天完成金额
            if($date_type == 'month')
            {
                $value->plan_money = $value->money;
                $value->completion_rate = number_format(($money/ $value->money),4)*100.."%";
            }else{
                $value->plan_money =  ceil($value->money/date("t"));        //    今天计划完成金额
                $value->completion_rate =  number_format(($value->today_money/ $value->plan_money),4)*100..'%';        //    今天完成率
            }

           if($money > $top_completion)
           {
               $top_completion = $money;
               $top_name =  $value->username;
           }
            $total_money = $total_money+ $money;
        }


        $data = [
            'top_completion'=>$top_completion,
            'top_name'=>$top_name,
            'total'=>$total_money,
            'users'=>$qq
        ];

        return self::ShowMessage($data);


    }


    /**
     * @param Request $request
     * 获取客服主页信息数据
     */
    public function getCustomerServiceData(Request $request)
    {
        $start_date = date('Y-m-d',time()-86400);
        $end_date  = date('Y-m-d H:i:s',time()-86400);
        $expired_date ='three';
        $date_type = 'day';

        //   用户token
        if(!$request->has('token') || empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');


        $public = new PublicController();

        //   获取用户ID
        $user = $public->getTokenById($token);

        if(empty($user))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }

        //  获取 近三天到期的 账户数量
        if($user[0]['level'] != 3)
        {
            return self::ShowError(ErrorDefine::INVALID_LEVEL);
        }



        if($request->has('expired_date') && !empty($request->input('expired_date')))
        {
            $expired_date = $request->input('expired_date');
        }


        $expired_at = date('Y-m-d H:i:s',time()+86400*3);

        switch($expired_date)
        {
            case 'three':$expired_at = date('Y-m-d H:i:s',time()+86400*3);break;
            case 'five':$expired_at = date('Y-m-d H:i:s',time()+86400*5);break;
            case 'seven':$expired_at = date('Y-m-d H:i:s',time()+86400*7);break;
        }


        if($request->has('date_type') && !empty($request->input('date_type')))
        {
            $date_type = $request->input('date_type');
        }



        $created_at = date('Y-m-d H:i:s',time());
        switch($date_type)
        {
            case 'day':$created_at = date('Y-m-d',time());break;
            case 'week':$created_at = self::getWeekTime();break;
            case 'month':$created_at = date('Y-m-01',time());break;
        }


        $manage_id = $user[0]['manage_id'];

        //  客服即将到期账号数量
        $count = Rad_users::where('manage_id',$manage_id)->where('expired_at','<',$expired_at)->count();

        $created_at = explode(',',trim($created_at,','));

        $weekarray=array("日","一","二","三","四","五","六");

        // 获取自己的销售额
        $team = Teamqq::where('manage_id',$manage_id)->first();

        $plan_money = $team->money;             //  计划销售额
        $days = date('t');                      //  每月的天数

        $avg_money = ceil($plan_money / $days);


        foreach($created_at as $key=>$value)
        {
            $time[$key]['start_at'] = $value;
            if($date_type == 'week')
            {
                $time[$key]['name'] ='星期'.$weekarray[date("w",strtotime($value))];
                $time[$key]['end_at'] = date('Y-m-d', strtotime($value)+86400);

            }else{
                $time[$key]['end_at'] = date('Y-m-d H:i:s',time());
            }
            $time[$key]['avg_money'] = $avg_money;
            $time[$key]['plan_money'] = $plan_money;
            $time[$key]['money'] = Rad_order::where('user_id',$manage_id)->where('status',1)->where('remove',0)->where('created_at','>',$value)->where('created_at','<',$time[$key]['end_at'])->sum('price');

        }


        //  已完成金额
        $completed_money = 0;

        $created_start = date('Y-m-01',time());
        $created_end = date('Y-m-d H:i:s',time());
        $qq = Teamqq::all();

        $completed = [];

        $manage = [];
        if(!empty($qq))
        {
            foreach($qq as $key=>$value)
            {
                $completed[$key]['money'] = Rad_order::where('user_id',$value->manage_id)->where('created_at','>',$created_start)->where('created_at','<',$created_end)->where('status',1)->sum('price');
                $completed[$key]['manage_id'] = $value->manage_id;
                $completed[$key]['username'] = $value->username;
            }
        }

        array_multisort($completed,SORT_DESC);
        $order = new Rad_order();
        $orders = $order->leftJoin('users','users.user_id','=','orders.to_user')->where('orders.user_id',$manage_id)
            ->where('orders.status',1)->where('orders.to_user','!=','0')->where('users.user_id','>','0')
            ->where('orders.account_type','付费')->where('orders.remove',0)
            ->orderBy('orders.created_at','desc')->limit(10)->get(['orders.to_user','orders.connect_num as con_max','orders.order_no','orders.date','orders.price','orders.created_at']);

        if(!empty($orders))
        {

            foreach($orders as &$item)
            {
                $users = Rad_users::where('user_id',$item['to_user'])->first();
                if($users != NULL)
                {
                    $item['username'] = $users->username;
                    $item['expired_at'] = $users->expired_at;

                    switch($users->set_meal_type)
                    {
                        case 1 : $item['meal_type'] = '基础套餐';break;
                        case 2 : $item['meal_type'] = '中级套餐';break;
                        case 3 : $item['meal_type'] = '高级套餐';break;
                    }

                }else{
                    $item['username'] = '';
                    $item['price'] = 0;
                }

            }
        }


        $data = [
            'count'=>$count,                    //  到期金额
            'time'=>$time,              //  获取自己的销售额
            'completed'=>$completed,            // 完成额度
            'users'=>$orders                     //  近期开通列表
        ];

        return self::ShowMessage($data);



    }

    /**
     * @param Request $request
     * 获取客服销售详情
     */
    public function getCustomerServicDetail(Request $request)
    {

        $start_date = date('Y-m-d',time()-86400*3);
        $end_date = date('Y-m-d H:i:s',time()-86400*3);
        $page_size = 20;
        $page_no = 0;
        $currently = 1;

        //   用户token
        if(!$request->has('token') || empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');


        $public = new PublicController();

        //   获取用户ID
        $user = $public->getTokenById($token);

        if(empty($user))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }

        if($user[0]['level'] != 2)
        {
            return self::ShowError(ErrorDefine::INVALID_LEVEL);
        }

        if(!$request->has('id') || empty($request->input('id')) || !is_numeric($request->input('id')))
        {
            self::ShowError(ErrorDefine::INVALID_PARAMS);
            return;
        }


        if(!$request->has('date_type') || empty($request->input('date_type')))
        {
            self::ShowError(ErrorDefine::INVALID_PARAMS);
            return;
        }

        $date_type = $request->input('date_type');

        switch($date_type)
        {
            case 'month':$start_date=date('Y-m-01',time());$end_date = date('Y-m-d H:i:s',time());break;
            case 'today':$start_date=date('Y-m-d',time());$end_date = date('Y-m-d H:i:s',time());break;
            default:self::ShowError(ErrorDefine::INVALID_PARAMS);return;
        }



        $manage_id = $request->input('id');

        $manage = Rad_manage::find($manage_id);

        if($manage == NULL)
        {
            self::ShowError(ErrorDefine::INVALID_PARAMS);
            return;
        }


        if($request->input('page_no'))
        {
            $page_no  = $request->input('page_no');
            $currently = $page_no;
            if($page_no){
                $page_no = $page_no - 1;
                if(is_numeric($page_no)){
                    if($page_no<0){
                        $page_no = 0;
                    }
                }
            }
        }
        if($request->input('page_size'))
        {
            $page_size = $request->input('page_size');
            if($page_size){
                if($page_size <=0 ){
                    $page_size =  20;
                }
            }
        }




        $count = Rad_order::where('user_id',$manage_id)->where('status',1)->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('account_type','付费')->count();

        $orders =  Rad_order::where('user_id',$manage_id)->where('status',1)->where('created_at','>',$start_date)->where('created_at','<',$end_date)->where('account_type','付费')
                   ->orderBy('created_at','desc')->offset($page_no*$page_size)->limit($page_size)->get(['order_no','to_user','price','connect_num','date','created_at']);

        $page_total = ceil($count/$page_size);


        foreach($orders as $value)
        {

            $user = Rad_users::find($value->to_user);
               if( $user == NULL){
                   continue;
               }

            switch($user->set_meal_type)
            {
                case 1:$value->meal_type = '基础套餐';break;
                case 2:$value->meal_type = '中级套餐';break;
                case 3:$value->meal_type = '高级套餐';break;
            }

        }

        $data = [
            'count'=>$count,
            'page_no'=>$currently,
            'page_total'=>$page_total,
            'orders'=>$orders,
        ];

        return self::ShowMessage($data);







    }

    /**
     * @param Request $request
     * 获取客服列表
     */
    public function getServiceList(Request $request)
    {

        //   用户token
        if(!$request->has('token') || empty($request->input('token')))
        {
            self::ShowError(ErrorDefine::TOKEN_NOT_EXIST);
            return;
        }
        $token = $request->input('token');


        $public = new PublicController();

        //   获取用户ID
        $user = $public->getTokenById($token);

        if(empty($user))
        {
            return self::ShowError(ErrorDefine::TOKEN_IS_EXPIRERD);
        }

        $team = Teamqq::where('manage_id','>',0)->get(['manage_id','username']);
        return  $team;
    }











    //  获取周时间

    static function getWeekTime()
    {
        $created_at = '';

        for($i=1;$i<=7;$i++)
        {
            $created_at .= date('Y-m-d', (time() - ((date('w') == 0 ? 7 : date('w')) - $i) * 24 * 3600)).','; //w为星期几的数字形式,这里0为周日

        }
        return $created_at;
    }



}


